Snowflake Data Cloud Summit 2020: 【国内事例】サイバーエージェント：広告配信プロダクトのSnowflakeへの移行 / 爆速で社内データ基盤をSnowflakeにしてみた話

モダンデータスタック(MDS)

#Snowflake

Seira

2020.12.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

2020年11月25日にSnowflake社が主催するバーチャルイベント、Data Cloud Summit 2020が開催されました。

Snowflake | Data Cloud Summit Japan 当エントリでは「【国内事例】サイバーエージェント：広告配信プロダクトのSnowflakeへの移行 / 爆速で社内データ基盤をSnowflakeにしてみた話」についてレポートします。

セッション概要

スピーカー

黒崎優太　氏, AI事業本部　Dynalyst開発責任者 | 株式会社サイバーエージェント
鷹雄健　氏, 全社システム本部　データ統括責任者 | 株式会社サイバーエージェント

内容

広告配信プロダクトのSnowflakeへの移行：月間数千億, 秒間数十万リクエスト規模の広告配信プロダクトで、Amazon RedshiftからSnowflakeへの移行をしました。採用してみてどうだったか、移行は大変だったのかなどをお話します。爆速で社内データ基盤をSnowflakeにしてみた話：Snowflakeを活用して最短1分でデータ連係を実現し、社内の様々なデータを集約しています。デモを中心に今すぐ出来るデータ活用法について話します。

セッションレポート

前半には、黒崎様によるRedshiftからSnowflakeへの移行についてのセッションがありました。

広告配信プロダクトのSnowflakeへの移行

1. 広告配信プロダクト Dynalystについて

Dynamic Retargeting for Games
- DSP事業者
- スマホ向けリターゲティング広告配信プラットフォーム
- トップセールス＠日本のスマホゲームの中でも高いシェア
- ユーザー毎に最適化した広告を配信
Website: https://www.dynalyst.io/ja/
開発しているシステム概況
- 入札リクエスト量：数十万リクエスト/秒
  - 数千億リクエスト/月
  - 入札トラフィック：約８Gbps
  - レスポンスタイム：100ms以内
  - ログの量：数TB/Day(圧縮状態)
  - DWHではSnowflakeを使用しているので、それ以外は全てAWSを使っている
DynalystでのDWHの活用
- アドホックな分析用途
  - 主にデータサイエンティストが分析で利用
- 機械学習バッチのデータソース
  - 条件に合うデータを抽出する
- Tableau(BIツール)上での分析や可視化
Tableauでの可視化
- エンジニア以外でもデータを把握しやくするためにダッシュボードを作成している

2. Snowflakeを採用してどうだったのか

Snowpipe
- S3等にデータをアップロードするとそのイベントをきっかけにファイルをSnowflakeに取り込む機能が非常に便利だった
  - 今まではETLを自前で実装していたが、その必要がなくなり、コードを書かずにデータの計画的な取り込みを実現
    - 5TB/日程度の流量でも取り込み遅延は1～2分程度
Task
- 定期実行したいようなクエリ(定時集計やテーブルのメンテナンスなど)がSnowflakeで完結する
  - 今までは外部のスケジューラーを使っていた
ログのローテーションのしやすさ
- 10TBくらいのテーブルに対して2TB分くらいの削除をした
  - DELETE FROM table WHERE logged_at≥=...
- 数秒でクエリが終了した
- メタデータの更新のみで、バックグラウンドで削除が行われるようで、大量のデータ削除が楽
- 一定期間経過するとexpireするような機能もあると更に嬉しいところ。。。
External Tables
- S3等にあるオブジェクトをテーブルに見立てて、直接スキャンが可能
  - Snowflakeのストレージに取り込んだ時よりクエリ速度は遅いが、取り込まずにクエリが可能
- カラム変換やパーテーションの定義に柔軟性があるため扱いやすい
シングルサインオン
- 社内のID基盤とSAML認証により連携することでパスワードレスなログイン環境を実現（Macの指紋認証など）
- 部署移動時に自動で権限が付与されたり、退職時に自動でアカウントが停止されるため、アカウントのメンテナンスが非常に楽
  - アカウント作成や職種に応じて違う権限を付与する仕組みは自社で実装
Work Sheet
- 実行計画がわかりやすい
  - 重いクエリの例
Snowsight
- 新しいUI（現時点ではPreview版）
  - 簡単なグラフやダッシュボードの作成はUIで完結してしまうので便利
    - 都度、BIツールに連携する必要がなくなり、簡単なものであればウェブ上で完結するので楽になった
    - クエリ結果をグラフで確認するのも簡単

3. Amazon RedshiftからSnowflakeへの移行にあたり検証したこと

クエリ性能
- 普段、分析で使うようなクエリが許容範囲の性能かどうか
  - 日常的に使う、重めのクエリで1‐2分以内に応答してほしい
- まずは1か月分のデータをインポートして性能を計測後、3か月分、半年分とデータ量を増やしていき、性能が大幅に悪化しないことを確認
- ウェアハウスのサイズを2倍にすると、多くのケースでクエリ時間もおおむね半分になることを確認
  - 普段はM-L、重いクエリはXLで運用することにした

結論：問題なし

DWHの安定性
- クエリが集中したり、重いクエリが走っているときにクエリ時間が極端に遅くなる状況は利用者にとってストレスなので避けたい
- Snowflakeは用途ごとにVirtual Warehouseが作成でき、それぞれのWarehouseは独立しているため、負荷の影響を受けない

結論：問題なし

コスト
- RedshiftからSnowflakeに移行するにあたって、コストが上がることは避けたい
- Redshiftは1‐3年のリザーブドインスタンスを購入するのが一般的
  - あらかじめコンピュートリソースを買いきる要素が強い、Redshift Spectrumは従量課金
- Snowflakeは従量課金の要素が強い
  - ウェアハウスが従量課金でコストの大半を占める
- Redshiftのクエリログのテーブルを集計することでクエリの頻度や量を概算
  - 人間以外が投げるクエリ（バッチ処理、Tableauなど）が多く見直しの必要があることがわかった

結論：クエリを投げるタイミングを集中させてウェアハウスの利用効率を上げたり、クエリの頻度を見直したりする必要があるが、結果的にコストは下がった

データ転送料
- AWSではInboundトラフィックは無料、Outboundトラフィックは有料
- AWS上で大量のデータを扱うプロダクトにとって外部のサービスを使う上でデータ転送料が問題になりがち
  - 例：東京リージョンからインターネットに1日5TB転送：約$3,000/月
- Snowflakeはクラウド事業者とリージョンが選べるので、同一リージョン内の通信にすることができる
  - AWS TokyoリージョンのS3からSnowflakeに転送する料金は無料（S3のAPI料金は発生）

結論：問題なし

運用のしやすさ
- クラスタのバージョンアップ作業などが不要（基本的にダウンタイムがない）
- 異常に重いクエリを投げてしまってもほかのウェアハウスに影響がでない
  - Virtual Warehouseを用途ごとに分ければいい
- ほぼすべての設定がSQLからも操作できて統一感がある
- Web UIが充実しており、設定項目も楽
- MySQLやPostgreSQLプロトコルが使えないのは欠点
  - Web UIが充実している部分でカバー
  - アダプタやインテグレーションが充実している

結論：問題なし（運用負荷は軽減した）

セキュリティ面
- 主にSnowflakeへの各種アプリケーションからのアクセス方法
- Snowflakeはインターネットに公開される前提で認証/認可や通信が暗号化されているため、どこからでもアクセスでき楽になった
  - 以前は、VPN経由でしかDWHにアクセスできなかった
  - IPアドレス制限やAWS Private Linkによるインターネットを経由しない通信も可能
- PostgreSQLプロトコルなどオープンなプロトコルが利用できない代わりに「ゼロトラスト」な環境になった

結論：問題なし（運用負荷は軽減した）

クエリの互換性
- Redshiftで使っていたクエリをできるだけそのまま使いたい
- アプリケーションから接続するドライバ
  - JDBCドライバが提供されているのでドライバの差し替えと設定項目の変更で補う
- IPアドレスをパ－スして正規化できるような関数など、Snowflakeの方が便利な関数も見つかった
- 今回の移行では機能的に不足しているケースはなかった

結論：おおよそ問題ないが、一部書き換えが必要な部分があった

(全てのクエリに対して移行前と移行後で同じ結果が返ってくるか事前確認するのを推奨)

4. 移行作業で行ったこと

DB作成～データのインポートまでの流れ
- Create database
- AWSのIAM Role作成
- S3インテグレーション作成
- AWS IAM Roleの信頼関係の更新
- ログフォーマット作成
- External Stageの作成
- テーブル作成
- テーブルへS3からデータをinsert
データ移行
- S3にExternal Stageを作成して、そこからSnowflakeのストレージに取り込む
- 大きめのWarehouseを複数使い並列に取り込むことで半年分のデータも数時間で移行完了
  - ComputeとStrageが分離されている恩恵を受けました（初期構築の時だけ大きめのリソースを使った）
クエリの互換性を保つための修正
- 整数同士の徐算が少数で返る
  - 1/100が0でなく、0.01になる
  - 整数も少数もnumber型で統一されているためだと思われる
  - floor関数で切り落とすことで互換性を維持
- random()関数が0～1の範囲外の値を返す
  - uniform(0.0, 1.0, random())で0～1の範囲に丸める
- snowsql(cli)にて
  - empty_for_null_in_tsv=trueを指定しないとnullが空文字として出力されない
- exis_on_error=trueを指定しないとクエリ失敗時にコマンド終了ステータスが成功になるのを気を付けないといけない
バッチ処理をしているアプリケーションの修正
- JDBCドライバを差し替えてリリース
  - jdbc:snowflale://で始める場合にSnowflakeのドライバを読み込むように修正
- Redshiftと並行稼働期間を作ったため、無停止での移行に成功
Tableauのデータソースの切り替え
- OAuth認証に対応しているため、Tableauのワークシートにクレデンシャルを埋める必要がないため安全
- 認証情報の使いまわしも防げる
- Snowflakeのint型がTableauだと小数点付きで解釈されてしまい、MySQLのジョインなどで困った
  - Snowflakeのint型はNUMBER 38桁で最大桁数がかなり大きいことが原因だったので、18桁であれば問題が起きなくなった
  - int型の定義をすべて18桁に設定することで解決した

5.　今後の挑戦したいこと

データレイクとしてのSnowflake
- 現状はS3をデータレイクとして利用していて、Snowflakeは分析用のログを格納場所としての利用に留まっている
- S3とSnowflake両方にログを格納しているためストレージコストが2重にかかっている
- 安定稼働し続け、S3上のログを直接参照する必要がなくなったらSnowflakeをメインのデータソースという位置づけを検討したい
Apache SparkのSnowflake Driver
- Apache Spark: クラスタコンピューティングフレームワーク
- 集計はApache Sparkを使って256コア、512GB程度のクラスタで実行
- データソースとしてSnowflakeを使うことで集計対象のデータ抽出や簡単な前処理をSnowflakeに寄せることができるので効率的になるのではないかと思っている
  （参考情報）https://docs.snowflake.com/ja/user-guide/spark-connector.html

社内データ基盤を爆速でSnowflakeにした話　～数日かかるデータ連携をSnowflake使って1分で実現

後半の15分セッションでは、鷹雄様による社内事例に関するセッションとなりました。

1. 自己紹介とサイバーエージェントについて

主にデータ関係のエンジニアとして、データ基盤の構築、データマートの整備、データ活用の促進、データコンサルなど幅広く業務をされている
メディア、広告、ゲーム事業を中心に子会社100社以上、連結従業員5000人以上

サイバーエージェントの情報システム組織である全社システム本部ではデータ活用に力を入れている
今年の始めにデータ活用のアイデアを部署内に応募したところ、742案が集まり、そのうち413案が可決となった
ほとんどのアイデアが単純にデータを分析するだけでなく、社内にあるデータを組み合わせる施策だったのでデータ基盤の整備が急務となった
当初のデータ活用の課題
- データが散らばっている
  - どこにあるのかわからない、活用までに時間がかかる
- データを一か所に集めるのに時間がかかる
  - 1か所にまとめたくてもシステム連携工数が発生
- データは可変する
  - 列名の変更・追加・削除やテーブルの追加などの度に連携調整

2. Snowflakeを選択

Snowflakeを活用した事例はすでに多数あるが、VPN/ZOOM利用率の可視化を紹介
- 緊急事態宣言発令後、97％の従業員がリモートに移行
- 必要なライセンス数の購入やトラフィック負荷状態可視化を基に用途別VPNへの切り替えなどデータを活用した経営判断をリアルタイムに行うのにSnowflakeが大活躍した

3. 爆速データ連携！Snowflakeデータ基盤の紹介

簡単システム構成
- システムからS3にCSVファイルを転送
- LambdaがCSVをNDJSONにコンバートしてS3に配置
- External Tableを利用してSnowflakeに取り込み
- 初回のみViewの作成
- TableauなどのBIツールからデータにアクセス

デモにて実際に紹介：動画21.44あたりから

デモの中で紹介した機能をピックアップ
- Externalテーブル：S3 などの外部ストレージにNDJSONファイルを置くだけでクエリができるようになります
  - https://docs.snowflake.com/ja/user-guide/tables-external-intro.html
- 半構造化データ型（VARIANT）
  - NDJSONを1カラムに読み込んでくれる凄い型
  - https://docs.snowflake.com/ja/sql-reference/data-types-semistructured.html
- AWSのS3とLambdaについて
  - Amazon S3：低コストで安全にデータを保存できるストレージ
    - ファイルのアップロードイベントでLambdaを実行できる
  - AWS Lambda: 低コストでサーバーレスプログラムを実行できる
- CSVからNDJSON変換用プログラムをGithubに公開中！
  https://github.com/ken-takao/csv2ndjson
  - Lambdaの設定とS3の設定をすれば今日から1分(以内)でSnowflake External Tableによる爆速データ連携が可能になるはず！
  - CSVコンバートと同時にView作成用クエリも自動生成
- CSV連携方法
  - S3をマウントしてcronによるファイルコピー
    - 特定のファイルを特定のパスに渡すのは1分もかからない
  - FluentdによるS3転送
    - 慣れている人であれば時間かからないと思う
  - Digdag+EmbulkによるS3転送
  - ETL（Asteria Warp）
    - 社内で利用しているKintoneをS3に渡すなど対応している
  - 沖縄のオペレーション部隊による人力連携
    - APIがなく自動的にデータを取得できないケースもあるので、ウェブページにアクセスしてcsvをS3にアップロード
- この基盤のメリット
  - 100％連携が可能
    - 連携先がどのようなシステムであれ、絶対にCSVは取得可能なはず
  - テーブルの定義・設計が不要
    - カラム追加・変更・削除された際に連携が止まらない（想像以上に絡む変更は多い）
    - カラムの定義はデータ利用時に使うものだけ変更
  - 過去のマスタ管理が可能
    - 連携ごとに2種類のViewを作成
    - 最新マスタと過去データView